package com.nbgs.demo.dao;

import com.nbgs.demo.info.LastConsumptionInfo;
import com.nbgs.demo.info.UserInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ShowUserMapper {
    //根据workerNum找出areaNum
    @Select("select area_num from t_area " +
            "join t_worker on t_worker.worker_id = t_area.worker_id " +
            "where t_worker.worker_num = #{workerNum}")
    String getAreaNumByWorkerNum(String workerNum);

    //根据用户id找到用户的姓名，卡号，水表号，用户住址
    @Select("select username,user_address,t_card.card_num,t_card.meter_id FROM " +
            "t_user join t_card on t_card.user_id = t_user.user_id  " +
            "where t_user.user_id =#{userId}")
    UserInfo getUserInfo(int userId);

    //根据用户id找到该用户的最近一次用水信息表中的月份，以及止度
    @Select("select consumption_id,consumption_month,consumption_start,consumption_state,consumption_end from t_consumption " +
            "join t_card on t_card.card_id = t_consumption.card_id " +
            "JOIN t_user on t_user.user_id = t_card.user_id " +
            "where t_user.user_id =#{userId}  " +
            "ORDER BY t_consumption.consumption_id DESC " +
            "LIMIT 0,1")
    LastConsumptionInfo selectLastConsumptionInfo(int userId);


    //刚进入页面时根据选择的条件查找员工所管理区域的所有的用户id
    @Select("<script>" +
            "select t_user.user_id from t_user join t_card " +
            "on t_card.user_id = t_user.user_id " +
            "join t_meter on t_meter.meter_id = t_card.meter_id " +
            "join t_type on t_card.type_id = t_type.type_id " +
            "<where>" +
            "<if test=\"areaNum!=''\" >" +
            " and t_card.area_num = #{areaNum} " +
            "</if>" +
            "<if test=\"meterId!=0\">" +
            "  and t_meter.meter_id like concat('%',#{meterId},'%') " +
            "</if>" +
            "<if test=\"type!=''\">" +
            "  and t_type.type = #{type} " +
            "</if>" +
            " limit #{currentPage},2" +
            "</where>" +
            "</script>")
    List<Integer> getUserIdByUserInfo(@Param(value="areaNum") String areaNum,
                                       @Param(value="meterId")int meterId,
                                       @Param("type") String type,
                                      @Param(value="currentPage")int currentPage);

    //刚进入页面时根据选择的条件查找员工所管理区域的所有的用户id
    @Select("<script>" +
            "select count(t_user.user_id) from t_user join t_card " +
            "on t_card.user_id = t_user.user_id " +
            "join t_meter on t_meter.meter_id = t_card.meter_id " +
            "join t_type on t_card.type_id = t_type.type_id " +
            "<where>" +
            "<if test=\"areaNum!=''\" >" +
            " and t_card.area_num = #{areaNum} " +
            "</if>" +
            "<if test=\"meterId!=0\">" +
            "  and t_meter.meter_id like concat('%',#{meterId},'%') " +
            "</if>" +
            "<if test=\"type!=''\">" +
            "  and t_type.type = #{type} " +
            "</if>" +
            "</where>" +
            "</script>")
    int getUserIdCount(@Param(value="areaNum") String areaNum,
                                      @Param(value="meterId")int meterId,
                                      @Param("type") String type);



    @Select("select bill_state from t_waterbill " +
            "join t_card on t_card.card_id = t_waterbill.card_id " +
            "join t_user ON t_user.user_id = t_card.user_id  " +
            "where t_user.user_id = #{userId}  " +
            "order by bill_generate_time DESC  " +
            "limit 0,1 ")
    String getBillState(int userId);





}